package tuding.android.bigplanettracks.maps.tuding;


import tuding.android.bigplanettracks.maps.RawTile;
import tuding.android.bigplanettracks.maps.storage.SQLLocalStorage;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class ActionNotes {

	private static final String DATABASE_NAME = "action_notes.db";

	public static final String TABLE_ACTION_NOTES = "action_notes";
	public static final String TABLE_ACTION_PHOTO = "action_photo";

	private static final String COLUMN_OFFSETY = "offsety";

	private static final String COLUMN_OFFSETX = "offsetx";

	private static final String COLUMN_X = "x";

	private static final String COLUMN_Y = "y";

	private static final String COLUMN_Z = "z";

	private static final String COLUMN_S = "s";

	private static final String COLUMN_DESCRIPTION = "description";
	private static final String COLUMN_URI = "uri";

	private static final String COLUMN_NAME = "name";
	
	private static final String COLUMN_DATE = "dateline";
	
	private static final String COLUMN_SPEED = "speed";
	private static final String COLUMN_ALT = "altitude";
	private static final String COLUMN_TRACKID = "trackid";
	
	private static final String COLUMN_LAT = "lat";
	
	private static final String COLUMN_LON = "lon";

	private static final String COLUMN_ID = "id";

	public static final String TABLE_DDL = "CREATE TABLE IF NOT EXISTS " + TABLE_ACTION_NOTES
			+ "(" + 
			COLUMN_ID + " integer primary key autoincrement," + 
			COLUMN_NAME + " text," + 
			COLUMN_DESCRIPTION + " text," +
			COLUMN_DATE + " long," +
			COLUMN_LAT + " float," +
			COLUMN_LON + " float," +
			COLUMN_SPEED + " integer," +
			COLUMN_ALT + " integer," +
			COLUMN_TRACKID + " integer," +
			COLUMN_OFFSETX + " integer," + 
			COLUMN_OFFSETY + " integer," + 
			COLUMN_S + " integer," + 
			COLUMN_X + " integer," + 
			COLUMN_Y + " integer," + 
			COLUMN_Z + " integer" + ");";

	public static final String TABLE_DDL_PHOTO = "CREATE TABLE IF NOT EXISTS " + TABLE_ACTION_PHOTO
			+ "(" + 
			COLUMN_ID + " integer primary key autoincrement," + 
			COLUMN_NAME + " text," + 
			COLUMN_DESCRIPTION + " text," +
			COLUMN_URI + " text," +
			COLUMN_DATE + " long," +
			COLUMN_LAT + " float," +
			COLUMN_LON + " float," +
			COLUMN_SPEED + " integer," +
			COLUMN_ALT + " integer," +
			COLUMN_TRACKID + " integer," +
			COLUMN_OFFSETX + " integer," + 
			COLUMN_OFFSETY + " integer," + 
			COLUMN_S + " integer," + 
			COLUMN_X + " integer," + 
			COLUMN_Y + " integer," + 
			COLUMN_Z + " integer" + ");";
	
	
	private static SQLiteDatabase db;

	public ActionNotes(Context context) {
		if (db == null) {
			String sqliteFilePath = SQLLocalStorage.TRACK_PATH + DATABASE_NAME;
			db = SQLiteDatabase.openDatabase(sqliteFilePath, null,
					SQLiteDatabase.CREATE_IF_NECESSARY);
			db.execSQL(TABLE_DDL);
			db.execSQL(TABLE_DDL_PHOTO);
		}
	}
	
	public ActionNotes() {
		if (db == null) {
			String sqliteFilePath = SQLLocalStorage.TRACK_PATH + DATABASE_NAME;
			db = SQLiteDatabase.openDatabase(sqliteFilePath, null,
					SQLiteDatabase.CREATE_IF_NECESSARY);
			db.execSQL(TABLE_DDL);
			db.execSQL(TABLE_DDL_PHOTO);
		}
	}
	


	public long saveActionNotes(ActionBase bookmark) {
		long rc = -1;
		if (bookmark.getId() != -1) {
			rc = updateActionNotes(bookmark);
		} else {
			ContentValues initialValues = new ContentValues();
			// initialValues.put(ActionNotes.COLUMN_ID, bookmark.getId());
			initialValues.put(ActionNotes.COLUMN_NAME, bookmark.getName());
			initialValues.put(ActionNotes.COLUMN_DESCRIPTION, bookmark.getDescription());
			initialValues.put(ActionNotes.COLUMN_S, bookmark.getTile().s);
			initialValues.put(ActionNotes.COLUMN_Z, bookmark.getTile().z);
			initialValues.put(ActionNotes.COLUMN_X, bookmark.getTile().x);
			initialValues.put(ActionNotes.COLUMN_Y, bookmark.getTile().y);
			initialValues.put(ActionNotes.COLUMN_DATE, bookmark.getDate());
			initialValues.put(ActionNotes.COLUMN_LAT, bookmark.getLat());
			initialValues.put(ActionNotes.COLUMN_LON, bookmark.getLon());
			initialValues.put(ActionNotes.COLUMN_SPEED, bookmark.getSpeed());
			initialValues.put(ActionNotes.COLUMN_ALT, bookmark.getAltitude());
			initialValues.put(ActionNotes.COLUMN_TRACKID, bookmark.getTrackID());

			initialValues.put(ActionNotes.COLUMN_OFFSETX, bookmark.getOffsetX());
			initialValues.put(ActionNotes.COLUMN_OFFSETY, bookmark.getOffsetY());
			// save to database
			rc = db.insert(ActionNotes.TABLE_ACTION_NOTES, null, initialValues);
		}
		return rc;
	}
	
	public long saveActionPhoto(ActionBase bookmark) {
		long rc = -1;
		if (bookmark.getId() != -1) {
			rc = updateActionPhoto(bookmark);
		} else {
			Log.i("IMAGE", "saveActionPhoto: "+bookmark.getUri().toString());
			ContentValues initialValues = new ContentValues();
			// initialValues.put(ActionNotes.COLUMN_ID, bookmark.getId());
			initialValues.put(ActionNotes.COLUMN_NAME, bookmark.getName());
			initialValues.put(ActionNotes.COLUMN_DESCRIPTION, bookmark.getDescription());
			initialValues.put(ActionNotes.COLUMN_URI, bookmark.getUri());
			initialValues.put(ActionNotes.COLUMN_S, bookmark.getTile().s);
			initialValues.put(ActionNotes.COLUMN_Z, bookmark.getTile().z);
			initialValues.put(ActionNotes.COLUMN_X, bookmark.getTile().x);
			initialValues.put(ActionNotes.COLUMN_Y, bookmark.getTile().y);
			initialValues.put(ActionNotes.COLUMN_DATE, bookmark.getDate());
			initialValues.put(ActionNotes.COLUMN_LAT, bookmark.getLat());
			initialValues.put(ActionNotes.COLUMN_LON, bookmark.getLon());
			initialValues.put(ActionNotes.COLUMN_SPEED, bookmark.getSpeed());
			initialValues.put(ActionNotes.COLUMN_ALT, bookmark.getAltitude());
			initialValues.put(ActionNotes.COLUMN_TRACKID, bookmark.getTrackID());

			initialValues.put(ActionNotes.COLUMN_OFFSETX, bookmark.getOffsetX());
			initialValues.put(ActionNotes.COLUMN_OFFSETY, bookmark.getOffsetY());
			// save to database
			rc = db.insert(ActionNotes.TABLE_ACTION_PHOTO, null, initialValues);
		}
		return rc;
	}
	
	

	public long updateActionNotes(ActionBase bookmark) {
		ContentValues args = new ContentValues();
		args.put(COLUMN_NAME, bookmark.getName());
		args.put(COLUMN_DESCRIPTION, bookmark.getDescription());
		long rc = db.update(TABLE_ACTION_NOTES, args, COLUMN_ID + "=" + bookmark.getId(), null);
		return rc;
	}
	
	public long updateActionPhoto(ActionBase bookmark) {
		ContentValues args = new ContentValues();
		args.put(COLUMN_NAME, bookmark.getName());
		args.put(COLUMN_DESCRIPTION, bookmark.getDescription());
		long rc = db.update(TABLE_ACTION_PHOTO, args, COLUMN_ID + "=" + bookmark.getId(), null);
		return rc;
	}

	public void removeActionNotes(int geoBookmarkId) {
		db.delete(TABLE_ACTION_NOTES, COLUMN_ID + "=" + geoBookmarkId, null);
	}
	
	public void removeActionPhoto(int geoBookmarkId) {
		db.delete(TABLE_ACTION_PHOTO, COLUMN_ID + "=" + geoBookmarkId, null);
	}
	
	public void removeActionNotesByTrackID(long trackID) {
		db.delete(TABLE_ACTION_NOTES, COLUMN_TRACKID+"="+trackID, null);
	}
	
	public void removeActionPhotoByTrackID(long trackID) {
		db.delete(TABLE_ACTION_PHOTO, COLUMN_TRACKID+"="+trackID, null);
	}
	
	public int getActionNotesNumByTrackID( int trackID ) {
		int rc = 0;
		if(trackID != 0) {
			try {
				Cursor c = db.query(true, TABLE_ACTION_NOTES, null, COLUMN_TRACKID + "=" + trackID, null,
						null, null, null, null);

				rc = c.getCount();
				c.close();
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		return rc;
	}
	
	public int getActionPhotoNumByTrackID( int trackID ) {
		int rc = 0;
		if(trackID != 0) {
			try {
				Cursor c = db.query(true, TABLE_ACTION_PHOTO, null, COLUMN_TRACKID + "=" + trackID, null,
						null, null, null, null);

				rc = c.getCount();
				c.close();
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		return rc;
	}
	
	public ActionBase getActionNotesByID( long actionID ) {
		ActionBase ab = new ActionBase();
		if(actionID != 0) {
			try {
				Cursor c = db.query(true, TABLE_ACTION_NOTES, new String[] {
						            COLUMN_ID,
						            COLUMN_NAME,
						            COLUMN_DESCRIPTION,
						            COLUMN_DATE,
						            COLUMN_LAT,
						            COLUMN_LON,
						            COLUMN_SPEED,
						            COLUMN_ALT,
						            COLUMN_TRACKID,
						            COLUMN_OFFSETX,
						            COLUMN_OFFSETY,
						            COLUMN_S,
						            COLUMN_X,
						            COLUMN_Y,
						            COLUMN_Z },
						            COLUMN_ID + "=" + actionID, 
						            null,
									null, 
									null, 
									null, 
									null );
				if (c != null) {
					c.moveToFirst();
					ab.setId(c.getInt(c.getColumnIndex(COLUMN_ID)));
					ab.setName(c.getString(c.getColumnIndex(COLUMN_NAME)));
					ab.setDescription(c.getString(c.getColumnIndex(COLUMN_DESCRIPTION)));
					ab.setLat(c.getFloat(c.getColumnIndex(COLUMN_LAT)));
					ab.setLon(c.getFloat(c.getColumnIndex(COLUMN_LON)));
					ab.setSpeed(c.getInt(c.getColumnIndex(COLUMN_SPEED)));
					ab.setAltitude(c.getInt(c.getColumnIndex(COLUMN_ALT)));
					ab.setTrackID(c.getInt(c.getColumnIndex(COLUMN_TRACKID)));
					ab.setOffsetX(c.getInt(c.getColumnIndex(COLUMN_OFFSETX)));
					ab.setOffsetY(c.getInt(c.getColumnIndex(COLUMN_OFFSETY)));
					ab.setDate(c.getLong(c.getColumnIndex(COLUMN_DATE)));
	
					RawTile tile = new RawTile(
							c.getInt(c.getColumnIndex(COLUMN_X)), 
							c.getInt(c.getColumnIndex(COLUMN_Y)), 
							c.getInt(c.getColumnIndex(COLUMN_Z)), 
							c.getInt(c.getColumnIndex(COLUMN_S)));
	
					ab.setTile(tile);
				}
				c.close();
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		return ab;
	}
	
	
	public ActionBase getActionPhotoByID( long actionID ) {
		ActionBase ab = new ActionBase();
		if(actionID != 0) {
			try {
				Cursor c = db.query(true, TABLE_ACTION_PHOTO, new String[] {
						            COLUMN_ID,
						            COLUMN_NAME,
						            COLUMN_DESCRIPTION,
						            COLUMN_URI,
						            COLUMN_DATE,
						            COLUMN_LAT,
						            COLUMN_LON,
						            COLUMN_SPEED,
						            COLUMN_ALT,
						            COLUMN_TRACKID,
						            COLUMN_OFFSETX,
						            COLUMN_OFFSETY,
						            COLUMN_S,
						            COLUMN_X,
						            COLUMN_Y,
						            COLUMN_Z },
						            COLUMN_ID + "=" + actionID, 
						            null,
									null, 
									null, 
									null, 
									null );
				if (c != null) {
					c.moveToFirst();
					ab.setId(c.getInt(c.getColumnIndex(COLUMN_ID)));
					ab.setName(c.getString(c.getColumnIndex(COLUMN_NAME)));
					ab.setDescription(c.getString(c.getColumnIndex(COLUMN_DESCRIPTION)));
					ab.setUri(c.getString(c.getColumnIndex(COLUMN_URI)));
					ab.setLat(c.getFloat(c.getColumnIndex(COLUMN_LAT)));
					ab.setLon(c.getFloat(c.getColumnIndex(COLUMN_LON)));
					ab.setSpeed(c.getInt(c.getColumnIndex(COLUMN_SPEED)));
					ab.setAltitude(c.getInt(c.getColumnIndex(COLUMN_ALT)));
					ab.setTrackID(c.getInt(c.getColumnIndex(COLUMN_TRACKID)));
					ab.setOffsetX(c.getInt(c.getColumnIndex(COLUMN_OFFSETX)));
					ab.setOffsetY(c.getInt(c.getColumnIndex(COLUMN_OFFSETY)));
					ab.setDate(c.getLong(c.getColumnIndex(COLUMN_DATE)));
	
					RawTile tile = new RawTile(
							c.getInt(c.getColumnIndex(COLUMN_X)), 
							c.getInt(c.getColumnIndex(COLUMN_Y)), 
							c.getInt(c.getColumnIndex(COLUMN_Z)), 
							c.getInt(c.getColumnIndex(COLUMN_S)));
	
					ab.setTile(tile);
				}
				c.close();
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		return ab;
	}
	
/*
	public List<ActionBase> getActionList() {
		List<ActionBase> bookmarks = new ArrayList<ActionBase>();
		try {
			Cursor c = db.query(true, ActionNotes.TABLE_ACTION_NOTES, null, null, null,
					null, null, null, null);

			int numRows = c.getCount();
			c.moveToFirst();
			for (int i = 0; i < numRows; ++i) {
				ActionBase bookmark = new ActionBase();
				bookmark.setId(c.getInt(c.getColumnIndex(COLUMN_ID)));
				bookmark.setName(c.getString(c.getColumnIndex(COLUMN_NAME)));
				bookmark.setDescription(c.getString(c.getColumnIndex(COLUMN_DESCRIPTION)));
				bookmark.setLat(c.getFloat(c.getColumnIndex(COLUMN_LAT)));
				bookmark.setLon(c.getFloat(c.getColumnIndex(COLUMN_LON)));
				bookmark.setSpeed(c.getInt(c.getColumnIndex(COLUMN_SPEED)));
				bookmark.setAltitude(c.getInt(c.getColumnIndex(COLUMN_ALT)));
				bookmark.setTrackID(c.getInt(c.getColumnIndex(COLUMN_TRACKID)));
				bookmark.setOffsetX(c.getInt(c.getColumnIndex(COLUMN_OFFSETX)));
				bookmark.setOffsetY(c.getInt(c.getColumnIndex(COLUMN_OFFSETY)));
				bookmark.setDate(c.getLong(c.getColumnIndex(COLUMN_DATE)));

				RawTile tile = new RawTile(
						c.getInt(c.getColumnIndex(COLUMN_X)), 
						c.getInt(c.getColumnIndex(COLUMN_Y)), 
						c.getInt(c.getColumnIndex(COLUMN_Z)), 
						c.getInt(c.getColumnIndex(COLUMN_S)));

				bookmark.setTile(tile);
				bookmarks.add(bookmark);
				c.moveToNext();
			}
			c.close();
		} catch (SQLException e) {
			Log.e("Exception on query", e.toString());
		}
		return bookmarks;
	}
	*/

	@Override
	public void finalize() {
		db.acquireReference();
	}
	
	public void close() {
		if (db != null) {
			db.close();
			db = null;
		}
	}
}
